#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date: 20140823
# Update Date: 20140826
# Update Date: 20140903 修正月份分区处理bug
#                       增加文件扫描功能,无需指定输入EXCEL文件
#
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'

"""从EXCEL文档解析数据库建表信息并生产DDL SQL。

利用XLRD模块解析excel文档，并将SQL写入到文件以便稍后提交到数据库执行。

  ClassFoo: 一行概述。
  gp_gen_ddl_from_excel(): 主要函数，实现主要功能。
"""

import os

import xlrd
import chardet

import kexshop.gtcom.commod

ddl_sql_file_name = 'gp_gen_ddl_from_excel.sql'
ddl_sql_file = open(ddl_sql_file_name, 'w')
ddl_sql_file.truncate()
ddl_sql_file.close()


def gp_gen_ddl_from_excel(excel_file):
    """从EXCEL文档解析表格数据并生产SQL。

    Args:
        excel_file: 选择的excel文件。

    Retruns:
        写入到gp_gen_ddl_from_excel.sql文件。
    """
    book = excel_file
    for shidx in range(book.nsheets):
        shname = book.sheet_by_index(shidx).name
        # for shname in book.sheet_names():
        sh = book.sheet_by_name(shname)

        # 依次获取各个字段信息
        database = sh.cell_value(rowx=0, colx=1)
        owner = sh.cell_value(rowx=0, colx=3)
        schema = sh.cell_value(rowx=0, colx=5)
        table = sh.cell_value(rowx=1, colx=1)
        table_commnet = sh.cell_value(rowx=1, colx=3)
        status = sh.cell_value(rowx=1, colx=5)

        distributed_type = sh.cell_value(rowx=2, colx=1)
        distributed_key = sh.cell_value(rowx=2, colx=3)
        appendonly = sh.cell_value(rowx=2, colx=5)

        orientation = sh.cell_value(rowx=3, colx=1)
        compresstype = sh.cell_value(rowx=3, colx=3)
        compresslevel = int(sh.cell_value(rowx=3, colx=5))

        one_part = sh.cell_value(rowx=4, colx=1)
        one_part_type = sh.cell_value(rowx=4, colx=3)
        one_part_key = sh.cell_value(rowx=4, colx=5)
        one_part_format = sh.cell_value(rowx=5, colx=1)
        one_part_start = sh.cell_value(rowx=5, colx=3)
        one_part_end = sh.cell_value(rowx=5, colx=5)
        two_part = sh.cell_value(rowx=6, colx=1)
        two_part_type = sh.cell_value(rowx=6, colx=3)
        two_part_key = sh.cell_value(rowx=6, colx=5)
        two_part_format = sh.cell_value(rowx=7, colx=1)
        two_part_start = sh.cell_value(rowx=7, colx=3)
        two_part_end = sh.cell_value(rowx=7, colx=5)

        start_commnet = '\n----------' + str(shidx + 1) + ' ' + shname + '----------\n'
        ddl_commnet = '\n-- Create table ' + schema + '.' + table + ' On Database[' + database + '] Owner [' + owner + '].\n--Drop table if exists ' + schema + '.' + table + ';\n'
        table_header_sql = '\nCreate table ' + schema + '.' + table + '\n'

        column_list_sql = ''
        index_sql = ''
        column_commnet_sql = ''
        with_sql = ''
        distributed_sql = ''
        one_part_sql = ''
        one_part_lsit_sql = ''
        table_owner_sql = ''
        table_commnet_sql = ''
        # 获取column信息
        for rx in range(10, sh.nrows):
            column_list_sql += sh.row_values(rx)[0] + ' ' + sh.row_values(rx)[1]
            if sh.row_values(rx)[2].upper() == 'N':
                column_list_sql += ' not null'
            if sh.row_values(rx)[3].upper() == 'Y':
                column_list_sql += ' primary key'
            if rx == sh.nrows - 1:
                pass
            else:
                column_list_sql += ',\n'

            # 获取index信息
            if sh.row_values(rx)[4].upper() == 'Y':
                index_sql += 'create index idx_' + table + '_' + sh.row_values(rx)[
                    0] + ' on ' + schema + '.' + table + '(' + \
                             sh.row_values(rx)[0] + ');\n'
            # column_commnet_sql
            if sh.row_values(rx)[5]:
                column_commnet_sql += "comment on column " + schema + '.' + table + '.' + sh.row_values(rx)[
                    0] + " is '" + \
                                      sh.row_values(rx)[5] + "';\n"
        column_list_sql = '(\n' + column_list_sql + '\n)\n'

        if appendonly.upper() == 'Y':
            with_sql = 'with (appendonly=true,orientation=' + orientation
            if compresstype.upper() == 'ZLIB':
                with_sql += ',compresstype=zlib,compresslevel=' + str(compresslevel)
            elif compresstype.upper() == 'QUICKLZ':
                with_sql += ',compresstype=quicklz'
            with_sql += ')'

        if distributed_type.upper() == 'HASH':
            distributed_sql = '\ndistributed by (' + distributed_key + ')';
        else:
            distributed_sql = '\ndistributed randomly'

        # 处理一级分区
        if one_part.upper() == 'Y':
            distributed_sql += '\n';
            one_part_sql = 'partition by ' + one_part_type + '(' + one_part_key + ')\n'

            if one_part_format.upper() == 'YYYYMMDD':
                one_part_start = str(int(one_part_start))
                one_part_end = str(int(one_part_end))
                daydict = kexshop.gtcom.commod.GetDayDict(one_part_start, one_part_end)
                for start, end in [(k, daydict[k]) for k in sorted(daydict.keys())]:
                    one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                    if start != one_part_end:
                        one_part_lsit_sql += ',\n'
                one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
            elif one_part_format.upper() == 'YYYYMM':
                one_part_start = str(int(one_part_start))
                one_part_end = str(int(one_part_end))
                mondict = kexshop.gtcom.commod.GetMonDict(one_part_start, one_part_end)
                for start, end in [(k, mondict[k]) for k in sorted(mondict.keys())]:
                    one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                    if start != one_part_end:
                        one_part_lsit_sql += ',\n'
                one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
            elif one_part_format.upper() == 'MM':
                for i in range(12):
                    start = i + 1
                    end = start + 1
                    one_part_lsit_sql += 'partition ' + one_part_key + str(start) + ' start(' + str(
                        start) + ') end (' + str(end) + ') ' + with_sql
                    if start != one_part_end:
                        one_part_lsit_sql += ',\n'
                one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
        else:
            distributed_sql += ';\n'

        # 写入到平面文件
        ddl_sql_file = open(ddl_sql_file_name, 'a')
        ddl_sql_file.write(
            start_commnet.encode('utf8') + ddl_commnet.encode('utf8') + table_header_sql.encode(
                'utf8') + column_list_sql.encode('utf8') + with_sql.encode(
                'utf8') + distributed_sql.encode('utf8') + one_part_sql.encode('utf8') + one_part_lsit_sql.encode(
                'utf8'))
        # print ddl_commnet + table_header+ column + with_sql + distributed_sql + one_part_sql + one_part_lsit_sql

        index_commnet = '\n-- Create Index\n'
        ddl_sql_file.write(index_commnet + index_sql)
        # print index_commnet + index

        table_commnet_comment = '\n-- Table comment\n'
        if table_commnet:
            table_commnet_sql = "comment on table " + schema + '.' + table + " is '" + table_commnet + "';"
        ddl_sql_file.write(table_commnet_comment + table_commnet_sql.encode('utf8'))
        # print  table_commnet_comment + table_commnet_sql

        column_commnet_commnet = '\n-- Column comment\n'
        ddl_sql_file.write(column_commnet_commnet + column_commnet_sql.encode('utf8'))
        # print  column_commnet_commnet + column_commnet

        table_owner_commnet = '\n-- Table owner\n'
        table_owner_sql = 'alter table ' + schema + '.' + table + ' owner to ' + owner + ';\n'
        ddl_sql_file.write(table_owner_commnet + table_owner_sql)
        # print table_owner_commnet + table_owner_sql
        ddl_sql_file.close()


if __name__ == "__main__":
    execl_file_list = []
    for i in os.listdir(os.path.curdir):
        if i.endswith('xlsx') and not i.startswith('~$'):
            codedetect = chardet.detect(i)["encoding"]
            execl_file_list.append(i.decode(codedetect))
    for excel_file in execl_file_list:
        excel_file = xlrd.open_workbook(excel_file)
        gp_gen_ddl_from_excel(excel_file)
